﻿using MusicStore.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;

namespace MusicStore.Controllers
{
    public class SampleLingAndLambdaControllerController : Controller
    {
        private MusicStoreDB db = new MusicStoreDB();
        //查询歌手表
        public ActionResult SearchTableAritist()
        {
            //用Linq表达式
            var artistList1 = from x in db.Set<Artist>() select x;
            //Lambda
            var artists1 = db.Set<Artist>().Select(x => x);

            //Linq单条件查询
            artistList1 = from c in db.Set<Artist>()
                          where c.Name.Contains("AC")//等效于SQL：where name like '%AC%'
                          select c;

            //Linq多条件查询
            artistList1 = from c in db.Set<Artist>()
                          where (c.Name.Contains("AC") && c.ArtistId == 5)
                          select c;

            //Lambda单条件查询
            artistList1 = db.Set<Artist>()
                .Select(x => x)
                .Where(x => x.Name.Contains("AC"));

            //Lambda多条件查询
            artistList1 = db.Set<Artist>()
                .Where(x => x.Name.Contains("AC") && x.ArtistId == 5);


            // Linq多表查询
          var artist = from a in db.Set<Album>()
                       join b in db.Genres on a.Genre.GenreId equals b.GenreId
                       select a;


            //Linq多表,带条件查询
            var query1 = from x in db.Set<Album>()
                         join b in db.Artists on x.Artist.ArtistId equals b.ArtistId
                         where b.Name.Contains("AC")
                         select (x);

            //Lambda多表查询
            var query3 = db.Set<Album>()
                .Join(db.Set<Genre>(), x => x.Genre.GenreId, y => y.GenreId, (x, y) => y);

            //查询Album、Artist,指定列表ArtistsName、AlbumTitle、Price
            var albumAndArtist = from a in db.Set<Album>()
                                 join b in db.Set<Artist>() on a.Artist.ArtistId equals b.ArtistId
                                 select new
                                 {
                                     ArtistName = a.Artist.Name,
                                     AlbumTitle = a.Title,
                                     Price = a.Price
                                 };

            //查询Album、Artist,指定列表ArtistsName、AlbumTitle、Price,要求只取第一条数据
            var query5 = db.Set<Album>()
                .Join(db.Set<Artist>(), a => a.Artist.ArtistId, b => b.ArtistId, (a, b) => a)
                .Select(a => (new { id = a.Title, price = a.Price, artist = a.Artist.Name }))
                .OrderBy(x => x.price);//价格升序排序:orderdy；降序排序：orderbydescending  
                //.FirstOrDefault();//取默认第一条数据
            return View();
        }
        // GET: SampleLingAndLambda

        //public static IQueryable<T> GetPagList<T,Tkey>(MusicStoreDB context,int pageSize,int pageIndex,Expression<Func<T,bool>>)
        //{
        //    var rows = context.Set<T>()
        //        .Where(where)
        //        .OrderBy(orderby);
        //}
        public ActionResult Index()
        {
            return View();
        }
        //public ActionResult Search(string s)//模糊查找
        //{
        //    var albums = db.Albums.Where(x => x.Title.Contains(s));
        //    return View(albums);
        //}

        //public ActionResult Search(int id)//精确查找
        //{
        //    var albums = db.Albums.Where(x => x.GenreId==id);
        //    return View();
        //}
        public ActionResult Search(string s)
        {
            var albums = db.Albums
                .Include("Artist")
                .Include("Genre")
                .Where(x => x.Title.Contains(s) || x.Genre.Name.Contains(s) || x.Artist.Name.Contains(s));
            return View(albums);
        }
        public ActionResult Details(int id)
        {
            //var album = db.Albums.Where(x => x.AlbumId==id).FirstOrDefault();
            var album = db.Albums.Find(id);
            return View();
        }
        public ActionResult Delete(int? id)
        {
            if (id==null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            var album = db.Albums.Find(id);
            if (album==null)
            {
                return HttpNotFound();
            }
            return View(album);
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Delete(int id)
        {
            Album album = db.Albums.Find(id);
            db.Albums.Remove(album);
            db.SaveChanges();
            return RedirectToAction("Search");
        }
        public ActionResult Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Album album = db.Albums.Find(id);
            if (album == null)
            {
                return HttpNotFound();
            }
            ViewBag.ArtistId = new SelectList(db.Artists, "ArtistId", "Name", album.ArtistId);
            ViewBag.GenreId = new SelectList(db.Genres, "GenreId", "Name", album.GenreId);
            return View(album);
        }

        // POST: Manages/Edit/5
        // 为了防止“过多发布”攻击，请启用要绑定到的特定属性，有关 
        // 详细信息，请参阅 http://go.microsoft.com/fwlink/?LinkId=317598。
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "AlbumId,AlbumArtUrl,ArtistId,GenreId,Price,Title")] Album album)
        {
            if (ModelState.IsValid)
            {
                db.Entry(album).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.ArtistId = new SelectList(db.Artists, "ArtistId", "Name", album.ArtistId);
            ViewBag.GenreId = new SelectList(db.Genres, "GenreId", "Name", album.GenreId);
            return View(album);
        }
    }
}